package com.manager.devicemanager.service;


import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.Statement;

@Service
@Slf4j
public class CommonCleanupService {

    @Value("${cleanup.retention-days:10}")
    private int retentionDays;

    @Value("${cleanup.parallel-execution:true}")
    private boolean parallelExecution;
    @Autowired
    private  JdbcTemplate jdbcTemplate;



    /**
     * 同步清理数据库
     */
    public void syncCleanupDatabase(String dbName) {
        try {
            log.info("开始清理数据库[{}]的bgw_job_stat_history表",dbName);

            // 1. 删除旧记录
            int deletedRows = deleteOldRecords(dbName);
            // 2. 执行VACUUM（完全脱离事务管理）
            if (deletedRows > 0) {
                executeVacuumWithoutTransaction( dbName);
            }

            log.info("数据库[{}]清理完成",dbName);
        } catch (Exception e) {
            log.error("数据库[{}]清理失败", e,dbName);
        }
    }

    /**
     * 异步清理数据库（并行处理）
     */
    @Async
    public void asyncCleanupDatabase(String dataSourceName) {
        syncCleanupDatabase(dataSourceName);
    }

    /**
     * 删除指定数据库的旧记录
     */
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public int deleteOldRecords(String dbName) {
        String sql = String.format(
                "DELETE FROM _timescaledb_internal.bgw_job_stat_history " +
                        "WHERE execution_finish < NOW() - INTERVAL '%d days'",
                retentionDays
        );
        int deletedRows = jdbcTemplate.update(sql);
        log.info("数据库[{}]已删除{}条历史记录", dbName, deletedRows);
        return deletedRows;
    }

    /**
     * 关键修复：使用原生Statement执行VACUUM，避免管道模式
     */
//    @Transactional(propagation = Propagation.REQUIRES_NEW)
    /**
     * 关键修复：完全脱离事务管理，在自动提交模式下执行VACUUM
     * 注意：此方法**不能添加@Transactional注解**
     */
    public void executeVacuumWithoutTransaction( String dbName) {
        Connection connection = null;
        Statement statement = null;
        try {
            // 获取原生连接（不通过Spring事务管理）
            connection = jdbcTemplate.getDataSource().getConnection();

            // 关键设置：启用自动提交（确保不在事务块中）
            if (!connection.getAutoCommit()) {
                connection.setAutoCommit(true);
            }

            // 创建普通Statement
            statement = connection.createStatement();

            // 执行VACUUM（此时处于自动提交模式，无事务包裹）
            String sql = "VACUUM FULL _timescaledb_internal.bgw_job_stat_history";
            statement.execute(sql);
            log.info("数据库[{}]已执行VACUUM FULL（非事务模式）", dbName);

        } catch (Exception e) {
            log.error("数据库[{}]执行VACUUM失败", dbName, e);
//            throw new RuntimeException("VACUUM执行失败", e);
        } finally {
            // 强制关闭资源（避免连接泄漏）
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
                log.error("资源关闭失败", e);
            }
        }
    }
}
